import sqlite3
import os
from datetime import datetime
from config import DATABASE_CONFIG
import threading

class HumanDetectionLogger:
    def __init__(self, db_path=None):
        """初始化人体检测日志记录器"""
        self.db_path = db_path or DATABASE_CONFIG['path']
        self.lock = threading.Lock()  # 添加线程锁
        self.init_database()
    
    def init_database(self):
        """初始化数据库表"""
        with self.lock:  # 使用锁保护数据库初始化
            conn = sqlite3.connect(self.db_path, timeout=20.0)  # 设置连接超时
            cursor = conn.cursor()
            
            # 创建人体检测日志表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS detection_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    device_id TEXT NOT NULL,
                    date TEXT NOT NULL,
                    start_time TEXT NOT NULL,
                    end_time TEXT,
                    duration INTEGER DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建每日统计表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS daily_summary (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    device_id TEXT NOT NULL,
                    date TEXT NOT NULL,
                    total_duration INTEGER DEFAULT 0,
                    detection_count INTEGER DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(device_id, date)
                )
            ''')
            
            conn.commit()
            conn.close()
    
    def start_detection(self, device_id, timestamp):
        """开始一次人体检测记录"""
        with self.lock:  # 使用锁保护数据库操作
            conn = sqlite3.connect(self.db_path, timeout=20.0)  # 设置连接超时
            cursor = conn.cursor()
            
            dt = datetime.fromisoformat(timestamp)
            date = dt.strftime("%Y-%m-%d")
            time_str = dt.strftime("%H:%M:%S")
            
            cursor.execute('''
                INSERT INTO detection_logs (device_id, date, start_time, end_time, duration)
                VALUES (?, ?, ?, ?, ?)
            ''', (device_id, date, time_str, time_str, 0))  # 初始时start_time和end_time相同，duration为0
            
            log_id = cursor.lastrowid
            conn.commit()
            conn.close()
            
            return log_id
    
    def end_detection(self, log_id, timestamp):
        """更新人体检测记录的结束时间"""
        with self.lock:  # 使用锁保护数据库操作
            conn = sqlite3.connect(self.db_path, timeout=20.0)  # 设置连接超时
            cursor = conn.cursor()
            
            dt = datetime.fromisoformat(timestamp)
            date = dt.strftime("%Y-%m-%d")
            end_time = dt.strftime("%H:%M:%S")
            
            # 更新记录的结束时间
            cursor.execute('''
                UPDATE detection_logs 
                SET end_time = ?
                WHERE id = ?
            ''', (end_time, log_id))
            
            # 计算持续时间（秒）
            cursor.execute('''
                SELECT device_id, start_time FROM detection_logs WHERE id = ?
            ''', (log_id,))
            
            result = cursor.fetchone()
            if result:
                device_id, start_time_str = result
                start_dt = datetime.strptime(f"{date} {start_time_str}", "%Y-%m-%d %H:%M:%S")
                end_dt = datetime.strptime(f"{date} {end_time}", "%Y-%m-%d %H:%M:%S")
                duration = int((end_dt - start_dt).total_seconds())
                
                # 更新持续时间
                cursor.execute('''
                    UPDATE detection_logs 
                    SET duration = ?
                    WHERE id = ?
                ''', (duration, log_id))
                
                # 更新每日统计
                self._update_daily_summary(device_id, date, duration)
            
            conn.commit()
            conn.close()
    
    def _update_daily_summary(self, device_id, date, duration):
        """更新每日统计"""
        # 注意：这个方法应该只在已经获得锁的情况下被调用
        conn = sqlite3.connect(self.db_path, timeout=20.0)  # 设置连接超时
        cursor = conn.cursor()
        
        # 获取当前统计
        cursor.execute('''
            SELECT total_duration, detection_count FROM daily_summary 
            WHERE date = ? AND device_id = ?
        ''', (date, device_id))
        
        result = cursor.fetchone()
        if result:
            # 更新现有记录
            total_duration, count = result
            cursor.execute('''
                UPDATE daily_summary 
                SET total_duration = ?, detection_count = ?
                WHERE date = ? AND device_id = ?
            ''', (total_duration + duration, count + 1, date, device_id))
        else:
            # 插入新记录
            cursor.execute('''
                INSERT INTO daily_summary (device_id, date, total_duration, detection_count)
                VALUES (?, ?, ?, ?)
            ''', (device_id, date, duration, 1))
        
        conn.commit()
        conn.close()

    def get_daily_summary(self, date):
        """获取指定日期的统计信息"""
        with self.lock:
            conn = sqlite3.connect(self.db_path, timeout=20.0)
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM daily_summary WHERE date = ?
            ''', (date,))
            
            result = cursor.fetchone()
            conn.close()
            
            return result
            
    def get_detection_logs(self, date):
        """获取指定日期的检测记录"""
        with self.lock:
            conn = sqlite3.connect(self.db_path, timeout=20.0)
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM detection_logs WHERE date = ? ORDER BY start_time
            ''', (date,))
            
            results = cursor.fetchall()
            conn.close()
            
            return results

# 全局实例
detection_logger = HumanDetectionLogger()

if __name__ == "__main__":
    # 测试代码
    logger = HumanDetectionLogger()
    print("人体检测日志记录器已初始化")